﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Utility.Helper;

namespace BLL
{
    public class YX_LOG_JZ_BLL : BaseBLL
    {
        public void Insert(YX_LOG_JZ entity)
        {
            var sql = @"INSERT INTO YX_LOG_JZ(
                            ID,COMAPNY_NAME,COMPANY_ID,
                            OPEAR_ID,OPEAR_NAME,OPEAR_DATE,CARRY_OVER_MONTH)
                        VALUES(
                            :ID,:COMAPNY_NAME,:COMPANY_ID,
                            :OPEAR_ID,:OPEAR_NAME,:OPEAR_DATE,:CARRY_OVER_MONTH)";
            entity.ID = Guid.NewGuid().ToString();
            var parms = ParmsHelper.GetParms();
            parms.Add(":ID", entity.ID, "string");
            parms.Add(":OPEAR_ID", entity.OPEAR_ID, "int");
            parms.Add(":OPEAR_NAME", entity.OPEAR_NAME, "string");
            parms.Add(":OPEAR_DATE", entity.OPEAR_DATE, "datetime");
            parms.Add(":COMPANY_ID", entity.COMPANY_ID, "int");
            parms.Add(":COMAPNY_NAME", entity.COMAPNY_NAME, "string");
            parms.Add(":CARRY_OVER_MONTH", entity.CARRY_OVER_MONTH, "string");
            NoQuery(sql, parms);
        }

        public void Update(YX_LOG_JZ entity)
        {
            var sql = @"UPDATE YX_LOG_JZ
                        SET
                            COMAPNY_NAME = :COMAPNY_NAME,COMPANY_ID = :COMPANY_ID,
                            OPEAR_ID = :OPEAR_ID,OPEAR_NAME = :OPEAR_NAME,
                            OPEAR_DATE = :OPEAR_DATE,CARRY_OVER_MONTH = :CARRY_OVER_MONTH
                        WHERE
                            ID = :ID";
            var parms = ParmsHelper.GetParms();
            parms.Add(":ID", entity.ID, "string");
            parms.Add(":OPEAR_ID", entity.OPEAR_ID, "int");
            parms.Add(":OPEAR_NAME", entity.OPEAR_NAME, "string");
            parms.Add(":OPEAR_DATE", entity.OPEAR_DATE, "datetime");
            parms.Add(":COMPANY_ID", entity.COMPANY_ID, "int");
            parms.Add(":COMAPNY_NAME", entity.COMAPNY_NAME, "string");
            parms.Add(":CARRY_OVER_MONTH", entity.CARRY_OVER_MONTH, "string");
            NoQuery(sql, parms);
        }

        public void Delete(string id)
        {
            var sql = @"DELETE FROM YX_LOG_JZ WHERE ID = :ID";
            var parms = ParmsHelper.GetParms();
            parms.Add(":ID", id, "string");
            NoQuery(sql, parms);
        }

        public List<YX_LOG_JZ> GetDataByPage(int cmyId, int skip, int take)
        {
            var sql = @"SELECT
                            *
                        FROM
                            (SELECT
                                ROW_NUMBER() OVER(ORDER BY T1.OPEAR_DATE DESC) AS R,
                                T1.*
                            FROM YX_LOG_JZ T1
                            WHERE
                                T1.COMPANY_ID = :N_COMPANY_ID) T1
                        WHERE
                            T1.R >= :START_NUMBER AND T1.R < :END_NUMBER";
            var parms = ParmsHelper.GetParms();
            parms.Add(":N_COMPANY_ID", cmyId, "int");
            parms.Add(":START_NUMBER", skip, "int");
            parms.Add(":END_NUMBER", skip + take, "int");
            var dt = Query(sql, parms);
            var entitys = new List<YX_LOG_JZ>();
            foreach (var item in dt.AsEnumerable())
            {
                var entity = new YX_LOG_JZ();
                entitys.Add(entity);
                entity.ID = item.GetValue<string>("ID");
                entity.COMAPNY_NAME = item.GetValue<string>("COMAPNY_NAME");
                entity.OPEAR_ID = item.GetValue<int>("OPEAR_ID");
                entity.OPEAR_NAME = item.GetValue<string>("OPEAR_NAME");
                entity.COMPANY_ID = item.GetValue<int>("COMPANY_ID");
                entity.OPEAR_DATE = item.GetValue<DateTime>("OPEAR_DATE");
                entity.CARRY_OVER_MONTH = item.GetValue<string>("CARRY_OVER_MONTH");
            }
            return entitys;
        }

        public int GetDataByCount(int cmyId)
        {
            var sql = @"SELECT
                            COUNT(1)
                        FROM YX_LOG_JZ T1
                        WHERE
                            T1.COMPANY_ID = :N_COMPANY_ID";
            var parms = ParmsHelper.GetParms();
            parms.Add(":N_COMPANY_ID", cmyId, "int");
            return GetCount(sql, parms);
        }

        public YX_LOG_JZ GetInfo(string id)
        {
            var sql = @"SELECT
                            T1.*
                        FROM YX_LOG_JZ T1
                        WHERE
                            T1.ID = :ID";
            var parms = ParmsHelper.GetParms();
            parms.Add(":ID", id, "string");
            var dt = Query(sql, parms);

            if (dt.Rows.Count == 0)
            {
                return null;
            }
            YX_LOG_JZ entity = new YX_LOG_JZ();
            var item = dt.Rows[0];
            entity.ID = item.GetValue<string>("ID");
            entity.COMAPNY_NAME = item.GetValue<string>("COMAPNY_NAME");
            entity.OPEAR_ID = item.GetValue<int>("OPEAR_ID");
            entity.OPEAR_NAME = item.GetValue<string>("OPEAR_NAME");
            entity.COMPANY_ID = item.GetValue<int>("COMPANY_ID");
            entity.OPEAR_DATE = item.GetValue<DateTime>("OPEAR_DATE");
            entity.CARRY_OVER_MONTH = item.GetValue<string>("CARRY_OVER_MONTH");
            return entity;
        }

        /// <summary>
        /// 判断当前月是否已经结转
        /// </summary>
        public bool IF_JZ(int cmyId, string cMonth)
        {
            var sql = "SELECT COUNT(1) FROM YX_LOG_JZ T1 WHERE T1.CARRY_OVER_MONTH = :CARRY_OVER_MONTH AND T1.COMPANY_ID = :COMPANY_ID";
            var parms = ParmsHelper.GetParms();
            parms.Add(":CARRY_OVER_MONTH", cMonth, "string");
            parms.Add(":COMPANY_ID", cmyId, "int");
            return GetCount(sql, parms) > 0 ? true : false;
        }

        public void JZ_YS(int cmyId, string cMonth)
        {
            var sql = @"SELELCT
                            T2.*
                        FROM QS_PLANWEEKAPPLY T1
                        INNER JOIN QS_PLANWEEK_DETAIL T2 ON T1.N_WEEKAPP_ID = T2.N_WEEKAPP_ID
                        INNER JOIN QS_PLANMONTHAPPLY T3 ON T1.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        WHERE
                            T1.N_BUSINESS_STATE = 2 AND
                            T2.C_USEMONTH = :CARRY_OVER_MONTH AND
                            T2.C_PRONAME = '3、其他收入'
                        ORDER BY
                            T1.D_END DESC
                        ";
            var parms = ParmsHelper.GetParms();
            parms.Add(":CARRY_OVER_MONTH", cMonth, "string");
            var dt = Query(sql, parms);
            if (dt.Rows.Count == 0)
            {
                throw new Exception("结转失败,因为当前月没有做周资金计划");
            }
            var item = dt.Rows[0];
            var weekId = item.GetValue<int>("N_WEEKAPP_ID");
            var dweekId = item.GetValue<int>("N_WEEKDETAIL_ID");
            var proId = item.GetValue<int>("N_PROID");

            sql = "SELECT SUM(T1.N_AMT)/10000 AS SUM1 FROM YX_BANK_DETAILLIST T1 WHERE  TO_CHAR(T1.D_TRANDATE,'yyyy-MM') = :CARRY_OVER_MONTH AND T1.N_STATE = 0";
            dt = Query(sql, parms);
            var sum = dt.Rows[0].GetValue<decimal>("SUM1");
        }
    }
}